Find worksheet cells that match specified criteria

One of the more elementary VBA tasks that any developer will perform is to find cells that meet some criteria.  VBA and Excel support only a primitive method, the Range.Find method that requires some amount of understanding.  To further compound the problem the documentation leaves a lot to be desired.

Here, we address both those issues.  First, more details about the Find method, including information on how to search for cells that meet certain formatting.

Then, a “find all” capability.  Microsoft enhanced the user interface to include a “find all” capability.  Unfortunately, and some versions after it was introduced in the UI, it is still absent from the Excel object model.  So, we look at programmatically implementing a “find all” capability at the worksheet level.

Finally, the code for Find All and several examples.

 

The Range.Find method[1]

Finds specific information in a range.

Syntax


expression .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

expression A variable that represents a Range object.

Parameters

Name

Required/Optional

Data Type

Description

What

Required

Variant

The data to search for. Can be a string or any Microsoft Excel data type.  When searching for all cells that match a particular format, use a zero length string (i.e., "") for this argument.

After

Optional

Variant

The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you do not specify this argument, the search starts after the cell in the upper-left corner of the range.

LookIn

Optional

Variant

The type of information. Can be xlValues, xlFormulas, or xlComments.

LookAt

Optional

Variant

Can be one of the following XlLookAt constants: xlWhole or xlPart.

SearchOrder

Optional

Variant

Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.

SearchDirection

Optional

XlSearchDirection

The search direction. Can be xlByRows or xlByColumns.

MatchCase

Optional

Variant

True to make the search case sensitive. The default value is False.

MatchByte

Optional

Variant

Used only if you have selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.

SearchFormat

Optional

Variant

Apply search format.  Can be True or False.  See Remarks for more on how to use SearchFormat.

Return Value

A Range object that represents the first cell where that information is found.

Remarks


This method returns Nothing if no match is found. The Find method does not affect the selection or the active cell.

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

You can use the FindNext and FindPrevious methods to repeat the search.

Unfortunately, FindNext does not respect the SearchFormat specification.  So, if the search criteria include format information, you cannot use FindNext to continue the search.  See the section on using the SearchFormat argument for more on how to correctly find all cells.

When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.

The below example lists the addresses of all the cells in the range A1:A500 on worksheet 1 that contain the value 5.

    With Worksheets(1).Range("a1:a500")

    Dim C As Range

    Set C = .Find(5, LookIn:=xlValues)

    If Not C Is Nothing Then

        Dim FirstAddress As String, Rslt As String

        FirstAddress = C.Address

        Do

            Rslt = Rslt & C.Address & ","

            Set C = .FindNext(C)

        Loop While C.Address <> FirstAddress

        Debug.Print Left(Rslt, Len(Rslt) - 1)

        End If

        End With

To find cells that match more complicated patterns, use a For Each...Next statement with the Like operator. For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters Cour. When the code finds a match, it changes the font to Times New Roman.

For Each c In [A1:C5]
    If c.Font.Name Like "Cour*" Then c.Font.Name = "Times New Roman"
    Next c

 

Processing a large number of cells with a ‘For…Each’ loop may consume significant computer resources.  Consequently, a consumer may perceive the behavior as an unresponsive program.

 

Using the SearchFormat argument

The SearchFormat argument facilitates a search for cells that match a desired format using a two-step process:

1)      Specify the desired format through the CellFormat object returned by the Application.FindFormat property

2)      In the Find method specify the SearchFormat argument as True

For example, to find cells that match a particular number format, use

    Application.FindFormat.NumberFormat = "General;-General;""-"""

Another example might be to display the address of the first cell that has a particular fill, say the ColorIndex 19, starting after the active cell.

    With Application.FindFormat

    .Clear

    .Interior.ColorIndex = 19

        End With

    MsgBox Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _

        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False, SearchFormat:=True).Address

To specify the fill color, use ColorIndex or Color or ThemeColor.  What is peculiar is that while Interior.ThemeColor works as expected, specifying a TintAndShade causes the Find method to fail.  So, the below does not work.

    With Range("c6").Interior

    .ThemeColor = xlThemeColorAccent2

    .TintAndShade = 0.5

        End With

    With Application.FindFormat

    .Clear

    With .Interior

    .ThemeColor = xlThemeColorAccent2

    .TintAndShade = 0.5

        End With

        End With

    MsgBox Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _

        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False, SearchFormat:=True).Address

As noted above FindNext will not yield the expected result when searching for a format.  So, one is forced to use the Find method inside the loop as in the example below, which lists the addresses of all the cells in the active worksheet that have a fill of red.

    With Application.FindFormat

    .Clear

    With .Interior

    .Color = RGB(255, 0, 0)

        End With

        End With

    Dim FirstCell As Range

    Set FirstCell = Cells.Find(What:="", After:=Cells(1, 1), _

        LookIn:=xlFormulas, LookAt:=xlPart, _

        SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False, SearchFormat:=True)

    If Not FirstCell Is Nothing Then

        Dim CurrCell As Range, Rslt As String

        Set CurrCell = FirstCell

        Do

            Rslt = Rslt & CurrCell.Address & ","

            Set CurrCell = Cells.Find(What:="", After:=CurrCell, _

                LookIn:=xlFormulas, LookAt:=xlPart, _

                SearchOrder:=xlByRows, SearchDirection:=xlNext, _

                MatchCase:=False, SearchFormat:=True)

            Loop Until CurrCell.Address = FirstCell.Address

        Debug.Print Left(Rslt, Len(Rslt) - 1)

        End If

 

Note that this approach, using the FindFormat property and the SearchFormat argument, will not find cells that display a format that is the result of conditional formatting.

 

Additional Information

For more on using the CellFormat object, see the Office 2013 documentation:

CellFormat Object (Excel) http://msdn.microsoft.com/en-us/library/office/ff839805(v=office.15).aspx

and

CellFormat Members (Excel) http://msdn.microsoft.com/en-us/library/office/ff839073.aspx

 

The FindAll function

One of the things I hate about the Range collection's Find method is how cumbersome it is to set up and use. Not only is it clumsy to detect when one has processed all the cells that meet the find criteria but it is also not easy to get all the cells as a single range. The FindAll function simplifies the use of the Find method.

Function FindAll(What, Optional SearchWhat As Variant, _

        Optional LookIn, _

        Optional LookAt, _

        Optional SearchOrder, _

        Optional SearchDirection As XlSearchDirection = xlNext, _

        Optional MatchCase As Boolean = False, _

        Optional MatchByte, _

        Optional SearchFormat) As Range

The function returns a range consisting of all the cells that match the specified criteria.  Consequently, it might contain multiple areas.

FindAll supports all the same arguments that the Find method with the exception that SearchWhat replaces the After argument. SearchWhat can be

·        nothing in which case the code searches the activesheet's usedrange

·        a specific worksheet or a single cell in which case the code searches that worksheet's usedrange, or

·        a specific range in which case the code searches just that range

To search for cells that meet a format criteria, use the same approach as in the section discussing the SearchFormat argument.  Specifically, use the Application.FindFormat property to specify the desired format.  Then, use the FindAll function with the SearchFormat argument set to True.

In the example below, the code searches column C of the used range for all cells that match the specified number format.

    Application.FindFormat.Clear

    Application.FindFormat.NumberFormat = "General;-General;""-"""

    MsgBox FindAll("", Application.Intersect( _

            ActiveSheet.UsedRange, Range("c:c")), _

        xlFormulas, xlPart, SearchFormat:=True).Address

 

The FindAll code and several examples

Below is the code for the FindAll function.  There should be no need to modify it for any custom criteria.  It is general enough that it can be used as a “drag and drop” solution.

Option Explicit

 

Function FindAll(What, Optional SearchWhat As Variant, _

        Optional LookIn, _

        Optional LookAt, _

        Optional SearchOrder, _

        Optional SearchDirection As XlSearchDirection = xlNext, _

        Optional MatchCase As Boolean = False, _

        Optional MatchByte, _

        Optional SearchFormat) As Range

    'LookIn can be xlValues or xlFormulas, _

     LookAt can be xlWhole or xlPart, _

     SearchOrder can be xlByRows or xlByColumns, _

     SearchDirection can be xlNext, xlPrevious, _

     MatchCase, MatchByte, and SearchFormat can be True or False. _

     Before using SearchFormat = True, specify the appropriate settings _

     for the Application.FindFormat object, e.g., _

     Application.FindFormat.NumberFormat = "General;-General;""-"""

    Dim aRng As Range

    If IsMissing(SearchWhat) Then

        On Error Resume Next

        Set aRng = ActiveSheet.UsedRange

        On Error GoTo 0

    ElseIf TypeOf SearchWhat Is Range Then

        If SearchWhat.Cells.Count = 1 Then

            Set aRng = SearchWhat.Parent.UsedRange

        Else

            Set aRng = SearchWhat

            End If

    ElseIf TypeOf SearchWhat Is Worksheet Then

        Set aRng = SearchWhat.UsedRange

    Else

        Exit Function                       '*****

        End If

    If aRng Is Nothing Then Exit Function   '*****

    Dim FirstCell As Range, CurrCell As Range

    With aRng.Areas(aRng.Areas.Count)

    Set FirstCell = .Cells(.Cells.Count)

        'This little 'dance' ensures we get the first matching _

         cell in the range first

        End With

    Set FirstCell = aRng.Find(What:=What, After:=FirstCell, _

        LookIn:=LookIn, LookAt:=LookAt, _

        SearchDirection:=SearchDirection, MatchCase:=MatchCase, _

        MatchByte:=MatchByte, SearchFormat:=SearchFormat)

    If FirstCell Is Nothing Then Exit Function          '*****

    Set CurrCell = FirstCell

    Set FindAll = CurrCell

    Do

        Set FindAll = Application.Union(FindAll, CurrCell)

        'Setting FindAll at the top of the loop ensures _

         the result is arranged in the same sequence as _

         the  matching cells; the duplicate assignment of _

         the first matching cell to FindAll being a small _

         price to pay for the ordered result

        Set CurrCell = aRng.Find(What:=What, After:=CurrCell, _

            LookIn:=LookIn, LookAt:=LookAt, _

            SearchDirection:=SearchDirection, MatchCase:=MatchCase, _

            MatchByte:=MatchByte, SearchFormat:=SearchFormat)

        'FindNext is not reliable because it ignores the FindFormat settings

        Loop Until CurrCell.Address = FirstCell.Address

    End Function



Below is a subroutine with several examples of how to use FindAll.

Sub ExamplesOfFindAll()

    'reset any prior find format condition

    Application.FindFormat.Clear

   

    'show the address of the range in the activesheet _

     that contains a value of 1

    MsgBox FindAll(1, , xlValues, xlWhole).Address

   

    'show the address of the range in the activesheet _

     that contains 1 as any part of the value

    MsgBox FindAll(1, , xlValues, xlPart).Address

   

    'show the address of the range in the activesheet _

     where the formula contains a open paren

    MsgBox FindAll("(", , xlFormulas, xlPart).Address

   

    'show the address of the cells in column C of the activesheet _

     that contain a zero

    Application.FindFormat.Clear

    Dim Rslt As Range

    MsgBox FindAll(0, Range("c:c"), xlFormulas, xlWhole).Address

   

    'if a custom number format applies to the entire column C, the below _

     will cause a major performance headache because the find will step _

     through every cell in column C!

    'MsgBox FindAll("", Range("c:c"), _

        xlFormulas, xlPart, SearchFormat:=True).Address

 

    'An alternative to the above is to limit the search to the usedrange.

    Application.FindFormat.Clear

    Application.FindFormat.NumberFormat = "General;-General;""-"""

    MsgBox FindAll("", Application.Intersect( _

            ActiveSheet.UsedRange, Range("c:c")), _

        xlFormulas, xlPart, SearchFormat:=True).Address

 

    'show the address of the range in column C that contains a zero and _

     the specified custom number format

    Application.FindFormat.Clear

    Application.FindFormat.NumberFormat = "General;-General;""-"""

    MsgBox FindAll(0, Range("c:c"), _

        xlFormulas, xlWhole, SearchFormat:=True).Address

   

    'show the address of the range of cells in column C within the _

     activesheet's usedrange that have a fill color of xlThemeColorAccent2

    Application.FindFormat.Clear

    Application.FindFormat.Interior.ThemeColor = xlThemeColorAccent2

    MsgBox FindAll("", Application.Intersect( _

            ActiveSheet.UsedRange, Range("c:c")), _

        xlFormulas, xlPart, SearchFormat:=True).Address

    End Sub

 

 



[1] Most of the information for the Range.Find method is from the Microsoft online documentation.  I have added missing content, clarified other content, made corrections, and provided additional information about the SearchFormat parameter.